#!/usr/bin/envpython
# -*-coding:UTF-8-*-
'''
@File    :   base.py
@Contact :   308711822@qq.com
@License :   (C) Copyright 2021-2225, Personal exclusive right.

@Modify Time      @Author    @Version    @Desciption
------------      -------    --------    -----------
2023/1/31 20:05   小钟同学      1.0         None
'''
import abc
import uuid
from datetime import datetime
from typing import TypeVar, Generic, Type, Dict, Union, List, Optional
from uuid import UUID

from fastapi import HTTPException
from sqlalchemy import text, select, func, delete, desc
from sqlalchemy.engine import ChunkedIteratorResult, CursorResult
from sqlalchemy.exc import NoResultFound
from sqlalchemy.ext.asyncio import AsyncSession as SQLAlchemyAsyncSession

from afast_core.core_db.sqlalchemy.sqlalchemy_modelbase import Base

# ORM数据库模型类型
ModelType = TypeVar("ModelType", bound=Base)

def datatime_str(item):
    for name, values in item.__dict__.items():
        if isinstance(values, datetime):
            item.__dict__[name] = str(values)
    return item

class BaseCRUDRepository(Generic[ModelType], metaclass=abc.ABCMeta):

    def __init__(self, async_session: SQLAlchemyAsyncSession,*args, **kwargs) -> None:
        self.async_session: SQLAlchemyAsyncSession = async_session

    @property
    @abc.abstractmethod
    def _model(self) -> Type[ModelType]:
        ...

    async def commit_session(self):
        """
        Commits the session if not in testing environment
        :return: None
        """
        await self.async_session.commit()

    async def get_by_id(self, id: str) -> Union[ModelType, None]:
        query = select(ModelType.name).filter(ModelType.id == id)
        try:
            item = (await self.async_session.execute(query)).scalar_one()
        except NoResultFound:
            return None
        return item

    async def get_by_ids(
            self, *, list_ids: List[Union[UUID, str]]) -> Optional[List[ModelType]]:
        response = await self.async_session.execute(select(self._model).where(self._model.id.in_(list_ids)))
        return response.scalars().all()

    async def get_count(self) -> Optional[ModelType]:
        response = await self.async_session.execute(select(func.count()).select_from(select(self._model).subquery()))
        return response.scalar_one()

    async def get_multi(
            self,
            *,
            skip: int = 0,
            limit: int = 100,
    ) -> List[ModelType]:
        query = select(self._model).offset(skip).limit(limit).order_by(self._model.id)
        response = await self.async_session.execute(query)
        return response.scalars().all()

    async def get_by_id_select(self, *, id: str | uuid.UUID) -> ModelType | None:
        statement = select(self._model).where(self._model.id == id)
        result: ChunkedIteratorResult = await self.async_session.execute(statement=statement)
        data: ModelType | None = result.scalar_one_or_none()
        return data

    async def read_multi(self) -> List[ModelType]:
        query = select(self._model)
        results = (await self.async_session.execute(query)).scalars()
        return results

    async def delete_by_id_delete(self, *, id: str | uuid.UUID) -> CursorResult:
        delete_statement = delete(self._model).where(self._model.id == id)
        result: CursorResult = await self.async_session.execute(statement=delete_statement)
        await self.async_session.commit()
        return result

    async def remove_by_id(self, id: int) -> int:
        """ 通过 id 删除对象 """
        sql = delete(self._model).where(self._model.id == id)
        result = await self.async_session.execute(sql)
        await self.async_session.commit()
        # SQLAlchemy的rowcount属性实际上是一个只读属性，用于表示最近执行的SELECT语句返回的行数。它有助于检测SQL语句是否成功执行，以及确定查询结果中包含多少行。
        # SQLAlchemy中的rowcount与rowcounty的区别是，rowcount只返回受影响的行数，
        # 而rowcounty返回所有行的数量，不管是否受影响。
        # rowcount只能在execute()方法执行完毕后调用，而rowcounty可以立即调用。
        return result.rowcount

    async def remove_multi_by_ids(self, ids: list):
        """ 同时删除多个对象 """
        id_list = [int(i) for i in ids]  # postgresql 字段类型限制
        sql = delete(self._model).where(self._model.id.in_(ids))
        result = await self.async_session.execute(sql)
        await self.async_session.commit()
        # SQLAlchemy的rowcount属性实际上是一个只读属性，用于表示最近执行的语句返回的行数。它有助于检测SQL语句是否成功执行，以及确定查询结果中包含多少行。
        # SQLAlchemy中的rowcount与rowcounty的区别是，rowcount只返回受影响的行数，
        # 而rowcounty返回所有行的数量，不管是否受影响。
        # rowcount只能在execute()方法执行完毕后调用，而rowcounty可以立即调用。
        return result.rowcoun

    async def get_paginated(self, name: str, pageIndex: int = 1, pageSize: int = 10) -> List[ModelType]:
        """ 验证用户 """
        filed_name = self._model.__table__.c[name]
        if pageIndex == -1 and pageSize == -1:
            sql = select(self._model).order_by(desc(filed_name))
        else:
            sql = select(self._model).offset((pageIndex - 1) * pageSize).limit(pageSize).order_by(desc(filed_name))
        result = await self.async_session.scalars(sql)
        # await self._db_session.close()  # 释放会话
        return result.all()

    async def execute_sql(self, query_sql: str, values: Dict):
        #  单个查询==============
        # query = "SELECT * FROM users WHERE id=:id AND user_status=1"
        # values = {"id": id}
        # result = await session.execute(text(query_sql), values)
        # result = result.fetchone()
        # return UserSchema(**result)
        # 插入===================
        # query = """
        #         INSERT INTO users (
        #           first_name,
        #           last_name
        #         )
        #         VALUES (
        #           :first_name,
        #           :last_name
        #         )
        #     """
        # values = {
        #     "first_name": user.first_name,
        #     "last_name": user.last_name,
        # }
        # 查询=============================
        # query = """
        #       UPDATE
        #         users
        #       SET
        #         chat_status = :chat_status,
        #         modified_date = :modified_date
        #       WHERE
        #         user_status = 1
        #         AND email = :email
        #   """
        # values = {
        #     "chat_status": chat_status,
        #     "email": currentUser.email,
        #     "modified_date": datetime.datetime.utcnow(),
        # }
        # 删除=-==========
        # query = """
        #         DELETE
        #         FROM
        #           members
        #         WHERE
        #           roomas = :roomas
        #         AND
        #           membersid = :membersid
        #     """
        # values = {"roomas": room_id, "membersid": user_id}

        result = await self.async_session.execute(text(query_sql), values)
        return result
